﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;

namespace cash_desk_model.src.security
{
    public class SecurityService
    {
        private const String _connString = @"Data Source=COCO-PC\COCO; Initial Catalog=CASH_DESK_DB; Integrated Security=true";
        private static SqlConnection _dbConn = null;

        public static void SetupDBConn()
        {
            // Create database connection
            _dbConn = new SqlConnection();
            _dbConn.ConnectionString = _connString;
            _dbConn.Open();
        }

        public static void DestroyDBConn()
        {
            // Close databacse connection
            _dbConn.Close();
        }

        public bool AthenticateUser(String userName, String password)
        {
            /// Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetPassword";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@userName", System.Data.SqlDbType.VarChar).Value = userName;
            SqlDataReader reader = command.ExecuteReader();

            // Get user password by user name 
            String dbPassword = "";
            if (reader.Read())
            {
                dbPassword = reader["PASSWORD"].ToString();
            }
            reader.Close();

            // Verify password
            if (dbPassword.Length > 0)
            {
                //if (dbPassword.Equals(password))
                if(Coder.verifyMd5Hash(password,dbPassword))
                {
                    return true;
                }
            }
            return false;
        }

        public bool CheckUser(String userName)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetUser";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@userName", System.Data.SqlDbType.VarChar).Value = userName;
            SqlDataReader reader = command.ExecuteReader();

            // Chek user 
            if (reader.Read())
            {
                reader.Close();
                return true;
            }
            reader.Close();
            return false;
        }

        public cash_desk_model.src.security.User GetAuthenticatedUser(String userName)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText="GetUser";
            command.Connection=_dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@userName", System.Data.SqlDbType.VarChar).Value = userName;
            SqlDataReader reader = command.ExecuteReader();

            // Get USer by user name
            User authenticatedUser = new User();
            if (reader.Read())
            {
                authenticatedUser.ID = Int32.Parse(reader["ID"].ToString());
                authenticatedUser.UserName = reader["USER_NAME"].ToString();
                authenticatedUser.FullName = reader["FULL_NAME"].ToString();
                authenticatedUser.Email = reader["EMAIL"].ToString();
                authenticatedUser.Address = reader["ADDRESS"].ToString();
                authenticatedUser.Status = reader["STATUS"].ToString();
                authenticatedUser.Password = reader["PASSWORD"].ToString();
                reader.Close();
                return authenticatedUser;
            }
            reader.Close();

            return null;
        }

        public String GetUserStatus(String userName)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetUserStatus";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@userName", System.Data.SqlDbType.VarChar).Value = userName;
            SqlDataReader reader = command.ExecuteReader();

            // Get user status by user name 
            String status = "";
            if (reader.Read())
            {
                status = reader["STATUS"].ToString();
                return status;
            }
            reader.Close();

            return null;
        }

        public static string GetPassword(string UserName)
        {
            // Call sql procedure
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Connection = _dbConn;

            // Get user password
            cmd.CommandText = "GetPassword";
            cmd.Parameters.Add("@userName", System.Data.SqlDbType.NVarChar, 50).Value = UserName;
            return cmd.ExecuteScalar().ToString();
        }

        public static void SetPassword(string UserName, string Password)
        {
            // Call sql procedure
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "ChangePassword";
            cmd.Connection = _dbConn;

            // Save new password
            cmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = UserName;
            cmd.Parameters.Add("@NewPass", System.Data.SqlDbType.NVarChar, 50).Value = Password;

            cmd.ExecuteNonQuery();
        }

        public int SaveNewUser(String userName, String fullName, String email,
            String address, String status, String password)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SaveNewUser";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@userName", userName));
            command.Parameters.Add(new SqlParameter("@fullName", fullName));
            command.Parameters.Add(new SqlParameter("@email", email));
            command.Parameters.Add(new SqlParameter("@address", address));
            command.Parameters.Add(new SqlParameter("@status", status));
            command.Parameters.Add(new SqlParameter("@password", password));

            return command.ExecuteNonQuery();
        }
    }
}
